package com.ajavaer.framework.core.orm;

import com.ajavaer.framework.common.message.PageMessage;
import com.ajavaer.framework.common.tools.JsonTools;
import com.ajavaer.framework.common.tools.ObjectTools;
import com.ajavaer.framework.common.tools.StringTools;
import com.ajavaer.framework.core.request.PagingRequest;
import com.ajavaer.framework.core.request.RequestFilter;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceUnit;
import javax.persistence.Query;
import java.util.*;

@Repository
public class RunSQL extends BaseRunSQL {
    private Logger log = LoggerFactory.getLogger(RunSQL.class);

    @PersistenceUnit
    public void setEntityManagerFactory(EntityManagerFactory emf) {
        this.emf = emf;
    }

    public List<Map<String, Object>> executeMap(String sql) {
        return executeMap(sql, new Object[0]);
    }

    public List<Map<String, Object>> executeMap(String sql, Map<String, Object> params) {
        return executeMap(sql, new Object[]{params});
    }

    public List<Map<String, Object>> executeMap(String sql, Object... params) {
        EntityManager newEm = this.emf.createEntityManager();
        try {
            Query query = createQuery(newEm, sql, params);
            query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            return query.getResultList();
        } finally {
            newEm.close();
        }
    }

    public <T> List<T> executeObject(String sql, Class<T> clazz) {
        return executeObject(sql, clazz, new Object[0]);
    }

    public <T> List<T> executeObject(String sql, Class<T> clazz, Object... params) {
        List<Map<String, Object>> mapResult = executeMap(sql, params);
        String json = JsonTools.beanToJson(mapResult);
        return JsonTools.jsonToList(json, clazz);
    }

    public long executeSQLForCount(String sql, Object... params) {
        EntityManager newEm = this.emf.createEntityManager();
        try {
            Query query = createQuery(newEm, sql, params);
            List resultList = query.getResultList();
            if (resultList.isEmpty()) {
                return 0l;
            }
            return Long.valueOf(resultList.get(0).toString());
        } finally {
            newEm.close();
        }
    }

    public PageMessage executePage(String select, String from, String groupBy, int page, int limit, String query, String sort) {
        return executePage(select, from, groupBy, new PagingRequest(page, limit, query, sort));
    }

    public PageMessage executePage(String select, String from, String groupBy, PagingRequest pagingRequest) {
        StringBuilder where = new StringBuilder(" where 1=1 ");
        List<Object> query = new ArrayList<>();
        for (Map.Entry<String, Object> searchItem : pagingRequest.getSearch().entrySet()) {
            String key = searchItem.getKey();
            Object value = searchItem.getValue();
            String[] fun = key.split(StringTools.UNDERLINE);
            if (fun.length != 2) {
                continue;
            }
            where.append(" and ").append(fun[1]);
            RequestFilter.Operator operator = RequestFilter.Operator.of(fun[0]);
            switch (operator) {
                case EQ:
                    where.append("=?");
                    query.add(value);
                    break;
                case LT:
                    where.append("<?");
                    query.add(value);
                    break;
                case GT:
                    where.append(">?");
                    query.add(value);
                    break;
                case LTE:
                    where.append("<=?");
                    query.add(value);
                    break;
                case GTE:
                    where.append(">=?");
                    query.add(value);
                    break;
                case IN:
                case NIN:
                    where.append(" ").append(operator == RequestFilter.Operator.IN ? "in" : "not in").append(" (");
                    if (value instanceof Collection) {
                        Iterator ite = ((Collection) value).iterator();
                        while (ite.hasNext()) {
                            where.append("?");
                            query.add(ite.next());
                            if (ite.hasNext()) {
                                where.append(",");
                            }
                        }
                    }
                    if (value instanceof Object[]) {
                        int length = ((Object[]) value).length;
                        for (int i = 0; i < length; i++) {
                            Object ite = ((Object[]) value)[i];
                            where.append("?");
                            query.add(ite);
                            if (i < length - 1) {
                                where.append(",");
                            }
                        }
                    }
                    if (value instanceof String && ((String) value).contains(",")) {
                        List<String> inList = StringTools.split((String) value, ",", String.class);
                        for (int i = 0; i < inList.size(); i++) {
                            String item = inList.get(i);
                            where.append("?");
                            query.add(item);
                            if (i < inList.size() - 1) {
                                where.append(",");
                            }
                        }
                    } else {
                        where.append("?");
                        query.add(value);
                    }
                    where.append(") ");
                    break;
                case NE:
                    where.append("<>?");
                    query.add(value);
                    break;
                case LIKE:
                    where.append(" like ?");
                    query.add("%" + value + "%");
                    break;
                case ISNULL:
                    where.append(" is null");
                    break;
                case ISNOTNULL:
                    where.append(" is not null");
                    break;
            }
            where.append(" ");

        }
        StringBuilder orderBy = new StringBuilder();
        Map<String, String> sortBy = pagingRequest.getSortBy();
        if (!sortBy.isEmpty()) {
            orderBy.append(" order by ");
            int size = sortBy.size();
            int now = 0;
            for (Map.Entry<String, String> sort : sortBy.entrySet()) {
                orderBy.append(sort.getKey()).append(" ").append(sort.getValue());
                if (++now < size) {
                    orderBy.append(",");
                }
            }
        }
        String whereSQL = " " + from + " " + where.toString() + " " + groupBy;
        return executePage(pagingRequest.getPage(), pagingRequest.getLimit(), select, whereSQL, orderBy.toString(), query.toArray());
    }

    public PageMessage executePage(int page, int limit, String select, String where, String orderBy, Object... params) {
        String count = "select count(1) from ({0}) total_rows";
        String limitSql = " limit " + ((page - 1) * limit) + "," + limit;
        String sql = select + " " + where;
        String countSql = StringTools.format(count, sql);
        sql += " " + orderBy;
        try {
            long totalCount = executeSQLForCount(countSql, params);
            List<Map<String, Object>> executeMap = executeMap(sql + limitSql, params);
            return PageMessage.of(page, limit, totalCount, executeMap);
        } catch (Exception e) {
            log.error("count sql: " + countSql);
            log.error("query sql: " + sql);
            log.error("query list: " + JsonTools.beanToJson(params));
            throw e;
        }
    }
}
